﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetStaffInStep_Ex]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetStaffInStep_Ex];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetStaffInStep_Ex]
    @DocID int
AS

DECLARE @flowid int
DECLARE @stepid int

IF EXISTS(SELECT 1 FROM uds_flow_document WHERE doc_id = @DocID)
BEGIN 
    SELECT     @flowid = flow_id,
        @stepid = step_id
        FROM 
            uds_flow_document
        WHERE
            doc_id = @docid

    --获得绑定角色里成员
    
    SELECT     A.*,
        datediff(yy,a.birthday,getdate()) as Age,
        (case sex when 1 then '男' else '女' end) as SexName,
        Convert(nvarchar(10),A.RegistedDate,120) AS RQ,
        (SELECT Position_name FROM uds_Position WHERE Position_id = b.Position_id) AS Position_Name
        FROM     uds_staff A,
            uds_staff_in_position b
        WHERE a.staff_id = b.staff_id
        and a.staff_id IN
            (
                SELECT     staff_id 
                    FROM 
                        v_uds_flow_staff_in_step
                    where
                            flow_id = @flowid 
                        and step_id = @stepid
                
            )
    RETURN 0
END
ELSE
    RETURN -1